package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import main.Main;
import objects.Project;


public interface IDBProject extends IDBTask {
	
	public class DBProject extends DBTask {
		
		/*
		 * methods are final for optimization of the code (by the compiler)
		 * it is not possible anymore to inherit it after this declaration 
		 */
		
		public void init() throws Exception {
			
			createTable();
			createTriggers();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table Project( " +
						   "Name varchar(" + maxLengthName + ") unique not null, " +
						   "Planning varchar(" + maxLengthPlanning + "), " +
						   "foreign key (Name) references Task (Name), " +
						   "primary key (Name) " +
						       "on conflict abort);";
	        
			executeCreation(query);
		}
		
		private final void createTriggers() throws Exception {
			
			String query;
			query = "create trigger update_project_name after update of Name on Project " +
			   	    "begin " +
				    	"update RelProjectStudent set Project_Name = new.Name " +
				    		"where (Project_Name = old.Name); " +
				    "end;";
			executeCreation(query);
			
			query = "create trigger delete_project_name before delete on Project " +
					"begin " +
	    	   			"delete from RelProjectStudent where (Project_Name = old.Name); " +
	    	   		"end;";
			executeCreation(query);
		}
		
		public final boolean add(String name, String desc, Boolean done, 
				Integer grades, String moreInfo, String plan) throws Exception {
			
			checkConsistance(name, desc, done, grades, moreInfo, plan);
			
			boolean success = super.add(name, desc, done, grades, moreInfo);
			
			Vector<Object> v = new Vector<Object>(2);
			v.add(0, name);
			v.add(1, plan);
			
			return (success && super.add("Project", v));
		}
		
		public final boolean updatePlanning(String name, String newPlan) throws Exception {
			
			checkName(name);
			checkPlanning(newPlan);
			
			return super.update("Project", "Name", name, "Planning", newPlan);
		}
		
		public final Project getProject(String name) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Project natural join Task " +
											 "where (Project.Name = '" + name + "');");
			if (rs.next()) {
				Project p = new Project(rs.getString("Name"), rs.getString("Description"), 
						rs.getBoolean("Done"), rs.getInt("Grades"),	rs.getString("MoreInformation"), 
						rs.getString("Planning"));
				rs.close();
				return p;
			}
			rs.close();
			return null;
		}
		
		public final Vector<Project> getAllProjects() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Project natural join Task;");
			
			Vector<Project> v = new Vector<Project>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new Project(rs.getString("Name"), rs.getString("Description"), 
					rs.getBoolean("Done"), rs.getInt("Grades"),	rs.getString("MoreInformation"), 
					rs.getString("Planning")));
				counter++;
			}
			rs.close();
			return v;
		}
		
		private final void checkConsistance(String name, String desc, Boolean done, 
				Integer grades, String moreInfo, String plan) throws Exception {
			
			super.checkConsistance(name, desc, done, grades, moreInfo);
			checkPlanning(plan);
		}
		
		private final void checkPlanning(String plan) throws Exception {
			
			if (plan == null) { plan = "No planning."; }
			if (plan.length() > maxLengthPlanning) {
				throw (new Exception("Planning exceeds maximum length (" +
						maxLengthPlanning + " characters)!"));
			}
		}
		
		
		private final int maxLengthPlanning = 500;
		
	}
	
}
